CREATE TABLE [dbo].[Content]
(
[Description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Keywords] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShowInTemplateFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishDateTime] [datetime] NULL,
[PublishSetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishLocation] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImportantUntilDate] [datetime] NULL,
[ExpirationDate] [datetime] NULL,
[ExpirationDays] [int] NULL,
[ExpSetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ArchiveAtExpirationFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReminderSentDateTime] [datetime] NULL,
[AutoCreatedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ListDefaultPublishLocation] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublicationDate] [datetime] NULL,
[SubscriptionDate] [datetime] NULL,
[PublishFrequency] [int] NOT NULL CONSTRAINT [DF_Content_PublishFrequency] DEFAULT ((0)),
[SuppressBannersFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContentID] [numeric] (18, 0) NOT NULL IDENTITY(2000, 1),
[WorkflowStatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalContentID] [numeric] (18, 0) NULL,
[PreviousContentID] [numeric] (18, 0) NULL,
[NavMenuID] [numeric] (18, 0) NULL,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[URLSafeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OpenInNewWindowFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FuseFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecureFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OwnerContactID] [numeric] (18, 0) NULL,
[ContactID] [numeric] (18, 0) NULL,
[OwnerSetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [numeric] (18, 0) NULL,
[MembersOnlyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedDate1] [datetime] NULL,
[UserDefinedDate2] [datetime] NULL,
[UserDefinedString1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedString2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedNumeric1] [numeric] (18, 0) NULL,
[UserDefinedNumeric2] [numeric] (18, 0) NULL,
[ComponentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Content_ComponentCode] DEFAULT ('CM'),
[PreFuseURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostFuseURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedDate3] [datetime] NULL,
[UserDefinedDate4] [datetime] NULL,
[UserDefinedDate5] [datetime] NULL,
[UserDefinedString3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedString4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedString5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefinedNumeric3] [numeric] (18, 0) NULL,
[UserDefinedNumeric4] [numeric] (18, 0) NULL,
[UserDefinedNumeric5] [numeric] (18, 0) NULL
) ON [PRIMARY]

GO
-- =============================================
-- This trigger sets OriginalContentID to the ID of the content inserted.
-- And sets SortOrder for the Content record if not specified.
-- =============================================
CREATE TRIGGER [dbo].[asi_Content_Insert]
    ON [dbo].[Content]
    FOR INSERT
AS
BEGIN
    -- Store OriginalContentID
    UPDATE [Content] SET OriginalContentID = ContentID
    WHERE ContentID IN (SELECT ContentID FROM inserted
                        WHERE OriginalContentID IS NULL)

    UPDATE [Content] SET SortOrder = (SELECT IsNull(Max(SortOrder),0) + 1 FROM [Content] x
                                    WHERE x.NavMenuID = [Content].NavMenuID)
    WHERE ContentID IN (SELECT ContentID FROM inserted
                        WHERE SortOrder IS NULL)
END

GO
-- ======================================================================
-- 06/27/2003  E.Tatsui  Trigger on Content table to set frequency in
--                       Publish_Request_Detail table.
-- ======================================================================
CREATE  TRIGGER [dbo].[asi_Content_Update]
    ON [dbo].[Content]
    FOR UPDATE
AS
BEGIN
    DECLARE
    @ContentID numeric,
    @PublishFrequency int

    IF UPDATE(PublishFrequency)
    BEGIN
        DECLARE c_Contents CURSOR FOR
        SELECT a.ContentID, IsNull(a.PublishFrequency,0) FROM inserted a, deleted b
        WHERE a.ContentID = b.ContentID
           AND IsNull(a.PublishFrequency,0) <> IsNull(b.PublishFrequency,0)
           AND IsNull(b.PublishFrequency,0) <> 0

        OPEN c_Contents
        FETCH NEXT FROM c_Contents
        INTO @ContentID, @PublishFrequency             

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @PublishFrequency > 0
                UPDATE Publish_Request_Detail
                   SET Frequency = @PublishFrequency * 60 * 60
                 WHERE ContentID = @ContentID
                   AND PublishRegenerateInd = 'P'
                   AND Frequency > 0
            ELSE
                DELETE FROM Publish_Request_Detail
                 WHERE ContentID = @ContentID
                   AND PublishRegenerateInd = 'P'
                   AND Frequency > 0

            FETCH NEXT FROM c_Contents
            INTO @ContentID, @PublishFrequency          
        END
        CLOSE c_Contents
        DEALLOCATE c_Contents
    END
END

GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED ([ContentID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [AK_Content_ContentID] UNIQUE NONCLUSTERED ([ContentID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Content_ComponentCode] ON [dbo].[Content] ([ComponentCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Content] ON [dbo].[Content] ([NavMenuID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Content_OriginalContentID] ON [dbo].[Content] ([OriginalContentID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Content_OwnerContactID] ON [dbo].[Content] ([OwnerContactID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Content_PreviousContentID] ON [dbo].[Content] ([PreviousContentID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Content_WorkflowStatusCode] ON [dbo].[Content] ([WorkflowStatusCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_Component_Ref] FOREIGN KEY ([ComponentCode]) REFERENCES [dbo].[Component_Ref] ([ComponentCode])
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_Content] FOREIGN KEY ([PreviousContentID]) REFERENCES [dbo].[Content] ([ContentID])
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_Content_OriginalContentID] FOREIGN KEY ([OriginalContentID]) REFERENCES [dbo].[Content] ([ContentID])
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_NavMenu] FOREIGN KEY ([NavMenuID]) REFERENCES [dbo].[Nav_Menu] ([NavMenuID])
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_Producer] FOREIGN KEY ([OwnerContactID]) REFERENCES [dbo].[Producer] ([ContactID])
GO
ALTER TABLE [dbo].[Content] ADD CONSTRAINT [FK_Content_WorkflowStatus] FOREIGN KEY ([WorkflowStatusCode]) REFERENCES [dbo].[Workflow_Status_Ref] ([WorkflowStatusCode])
GO
GRANT REFERENCES ON  [dbo].[Content] TO [IMIS]
GRANT SELECT ON  [dbo].[Content] TO [IMIS]
GRANT INSERT ON  [dbo].[Content] TO [IMIS]
GRANT DELETE ON  [dbo].[Content] TO [IMIS]
GRANT UPDATE ON  [dbo].[Content] TO [IMIS]
GO